package czu.caohao.utils;

import ch.qos.logback.core.util.ExecutorServiceUtil;
import ch.qos.logback.core.util.TimeUtil;
import com.fasterxml.jackson.databind.util.BeanUtil;
import czu.caohao.pojo.DBMasterStatus;
import czu.caohao.pojo.DBServer;
import czu.caohao.pojo.DBSlaveStatus;
import jdk.nashorn.internal.codegen.CompilerConstants;
import org.apache.commons.beanutils.BeanUtils;
import org.springframework.util.CommonsLogWriter;
import org.springframework.util.StringUtils;

import javax.persistence.metamodel.ListAttribute;
import java.lang.reflect.InvocationTargetException;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.*;

public class SqlUtils {
    //   key : server的id， value : dbServer
    private Map<Integer, DBServer> serverMap;
    //   key : server的id， value ： server对应的连接
    private Map<Integer, Connection> connMap;

    // 获取所有server连接的超时时间
    private static int TIME_OUT = 60;

    private static int CONN_TIMEOUT = 3000;

    // 线程池大小
    private static int THREAD_NUM = 10;


    private ExecutorService executorService;

    public Map<Integer, DBServer> getServerMap() {
        return serverMap;
    }

    public void setServerMap(Map<Integer, DBServer> serverMap) {
        this.serverMap = serverMap;
    }

    /**
     * 实例化时就获取所有连接
     *
     * @param serverMap
     */
    public SqlUtils(Map<Integer, DBServer> serverMap) {
        this.serverMap = serverMap;
        this.executorService = Executors.newFixedThreadPool(SqlUtils.THREAD_NUM);
        connMap = new HashMap<>();
        this.reflushConns();
    }

    /**
     * 向该serverMap中增加一个server，也需要在connMap中增加一个conn
     *
     * @return
     */
    public boolean addServer(DBServer server) {
        // 将该server 添加到serverMap中
        this.serverMap.put(server.getId(), server);

        String host = server.getServerIp();
        Integer port = server.getServerPort();
        String username = server.getUsername();
        String pass = server.getPass();
        Connection conn = getConn(host, port, username, pass);
        // 将获取的conn连接添加到connMap中
        connMap.put(server.getId(), conn);
        return true;
    }

    /**
     * 删除某个server
     *  删除serverMap和connMap中该服务器的实例
     * @param serverId
     * @return
     */
    public boolean deleteServer(Integer serverId){
        // 删除该节点的conn
        Connection conn = connMap.get(serverId);
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
            }
        }
        connMap.remove(serverId);

        // 从serverMap中删除该节点
        serverMap.remove(serverId);
        return true;
    }

    /**
     * 获取某个服务器的conn
     *      1.先从connMap中查找是否存在未关闭的conn，如果存在就返回，否则到第二步
     *      2.在serverMap中找到该id的server，调用getConn获取到该server的conn,并将该conn
     *          放入到connMap中
     *
     * @return
     */
    private Connection getServerConn(Integer id){
        Connection conn = connMap.get(id);
        boolean isClosed = true;
        try {
            isClosed = conn.isClosed();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        if(conn==null || isClosed){
            DBServer server = serverMap.get(id);
            if(server==null){
                return null;
            }
            else {
                conn = getConn(server.getServerIp(),server.getServerPort(),server.getUsername(),server.getPass());
                if(conn == null){
                    return null;
                }
                connMap.put(id,conn);
                return conn;
            }
        }else {
            return connMap.get(id);
        }
    }

    /**
     * 关闭id服务器的主从备份
     * @param id
     * @return
     */
    public boolean stopSlave(Integer id){
        Connection conn = getServerConn(id);

        // 如果执行到这里，说明能拿到conn，并且conn没有关闭
        Statement stop_slave = null;
        try {
            stop_slave = conn.createStatement();
            ResultSet rs = stop_slave.executeQuery("stop slave");
            return true;
        } catch (SQLException e) {
            return false;
        }
    }

    public boolean startSlave(Integer id){
        Connection conn = getServerConn(id);
        // 如果执行到这里，说明能拿到conn，并且conn没有关闭
        Statement stop_slave = null;
        try {
            stop_slave = conn.createStatement();
            ResultSet rs = stop_slave.executeQuery("start slave");
            return true;
        } catch (SQLException e) {
            return false;
        }
    }
  /**
     * 获取该serverMap中所有server的连接
     *
     * @return
     */
    public Map<Integer, Connection> getConnMap() {
        this.reflushConns();
        return this.connMap;
    }

    /**
     * 重新获取所有的连接，采用线程池并发加速获取
     */
    public boolean reflushConns() {
        // 关闭所有连接
        for (Integer id : connMap.keySet()){
            try {
                if(connMap.get(id)!=null){
                    connMap.get(id).close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        // 清空connMap
        connMap.clear();

        // 遍历所有的server，获取连接(使用连接池)
        List<Callable<Connection>> callables = new ArrayList<>();
        // 生成一个list，里面存放的是获取所有连接的任务（callable）
        serverMap.values().forEach((s) ->
        {
            Callable c = new Callable() {
                @Override
                public Object call() throws Exception {
                    Connection conn = getConn(s.getServerIp(), s.getServerPort(), s.getUsername(), s.getPass());
                    connMap.put(s.getId(), conn);
                    // 如果conn为null表示该server无法连接，设置他的状态为下线
                    if (conn == null) {
                        serverMap.get(s.getId()).setServerStatus(DBServer.DBServerStatus.OFFLINE_STATUS);
                    }
                    return conn;
                }
            };
            callables.add(c);
        });

        try {
            this.executorService.invokeAll(callables);
            //等待线程池执行完所有的获取连接的任务
            this.executorService.shutdown();
            boolean result = this.executorService.awaitTermination(SqlUtils.TIME_OUT, TimeUnit.SECONDS);
            executorService = Executors.newFixedThreadPool(SqlUtils.THREAD_NUM);
            return result;
        } catch (Exception e) {
            // 当发生中断时，打印错误，并且返回错误
            e.printStackTrace();
            return false;
        }
    }


    /**
     * 获取所有db-server的详细信息，包括master和slave——status
     *
     * @return
     */
    public Map<Integer, DBServer> getAllServerInfos() {
        // 每次获取所有服务状态前需要刷新conn
        if(!this.reflushConns()) return null;
        // 对于每个连接，获取master和slave状态,生成Collection<callable>集合让线程池执行
        List<Callable<DBServer>> callables = new ArrayList<>();
        connMap.forEach((id, conn) -> {
            // 任务，查询server对应的master_status和slave_status，设置到server中
            Callable call = new Callable() {
                @Override
                public Object call() throws Exception {
                    DBServer server = serverMap.get(id);
                    if ((conn!=null && conn.isClosed())||(conn == null && server != null)) {
                        server.setServerStatus(DBServer.DBServerStatus.OFFLINE_STATUS);
                    } else if (server != null) {
                        // 该数据库服务器上线状态
                        server.setServerStatus(DBServer.DBServerStatus.ONLINE_STATUS);
                        // 获取该服务器master_status
                        DBMasterStatus masterStatus = getMasterStatus(conn);
                        // 获取该服务器slave_status
                        DBSlaveStatus slaveStatus = getSlaveStatus(conn);
                        server.setDbMasterStatus(masterStatus);
                        server.setDbSlaveStatus(slaveStatus);
                        // 判断server身份，如果slave_status中没有主节点ip和port则默认他为主节点
                        if ((slaveStatus == null) || slaveStatus!=null
                                && StringUtils.isEmpty(slaveStatus.getMaster_host())
                                && StringUtils.isEmpty(slaveStatus.getMaster_port())) {
                            server.setServerIdentity(DBServer.DBServerIdentity.MASTER);
                        } else {
                            server.setServerIdentity(DBServer.DBServerIdentity.SLAVE);
                        }

                        // 判断slave主节点的id
                        if (server.getServerIdentity().equals(DBServer.DBServerIdentity.SLAVE)) {
                            String masterHost = server.getDbSlaveStatus().getMaster_host();
                            String masterPort = server.getDbSlaveStatus().getMaster_port();
                            // 从serverMap中找到是否有一个server的host和port跟他对应
                            for (DBServer s: serverMap.values()){
                                if (masterHost.equals(s.getServerIp()) && masterPort.equals(s.getServerPort().toString())) {
                                    server.setMasterServerId(s.getId());
                                    break;
                                }
                            }
                        }
                    }
                    return server;
                }
            };
            callables.add(call);
        });

        try {
            executorService.invokeAll(callables);
            executorService.shutdown();
            executorService.awaitTermination(SqlUtils.TIME_OUT, TimeUnit.SECONDS);
            executorService = Executors.newFixedThreadPool(SqlUtils.THREAD_NUM);
            return this.serverMap;
        } catch (Exception e) {
            // 执行获取所有server详情的线程池被中断
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 根据host,port,username,pass建立连接
     *
     * @param host
     * @param port
     * @param username
     * @param pass
     * @return
     */
    public Connection getConn(String host, Integer port, String username, String pass) {
        try {
            if (StringUtils.isEmpty(host)
                    || StringUtils.isEmpty(port)
                    || StringUtils.isEmpty(username)
                    || StringUtils.isEmpty(pass)) {
                return null;
            }
            final ExecutorService exec = Executors.newFixedThreadPool(1);

            Callable<Connection> call = new Callable<Connection>() {
                public Connection call() throws Exception {
                    //开始执行耗时操作
                    Class.forName("com.mysql.cj.jdbc.Driver");
                    String URL = "jdbc:mysql://" + host + ":" + port + "/mysql?serverTimezone=GMT%2B8";
                    Connection conn = DriverManager.getConnection(URL, username, pass);
                    return conn;
                }
            };
            Connection conn = null;
            try {
                Future<Connection> future = exec.submit(call);
                conn = future.get(1000 * 5, TimeUnit.MILLISECONDS);
            } catch (TimeoutException ex) {
                System.out.println("获取连接超时....");
                ex.printStackTrace();
                return null;
            } catch (Exception e) {
                System.out.println("处理失败.");
                e.printStackTrace();
                return null;
            }
            // 关闭线程池
            exec.shutdown();
            return conn;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }


    /**
     * 根据给的conn获取该server的slave_status
     *
     * @param conn
     * @return
     */
    private DBSlaveStatus getSlaveStatus(Connection conn) {
        DBSlaveStatus dbSlaveStatus = null;
        if (conn != null) {
            Statement st = null;
            try {
                st = conn.createStatement();
                ResultSet rs = st.executeQuery("show slave status");
                // 当rs没有数据时直接返回null
                if(!rs.next()) return null;
                dbSlaveStatus = new DBSlaveStatus();
                // 将rs对象中字段映射到dbSlaveStatus对象中
                ResultSetMetaData metaData = rs.getMetaData();
                for (int i = 1; i < metaData.getColumnCount() + 1; i++) {
                    BeanUtils.setProperty(dbSlaveStatus, metaData.getColumnName(i).toLowerCase(), rs.getObject(i));
                }
                return dbSlaveStatus;
            } catch (SQLException e) {
                e.printStackTrace();
                return null;
            } catch (IllegalAccessException e) {
                e.printStackTrace();
                return null;
            } catch (InvocationTargetException e) {
                e.printStackTrace();
                return null;
            }
        }
        return null;
    }

    private DBMasterStatus getMasterStatus(Connection conn) {
        DBMasterStatus dbMasterStatus = null;
        if (conn != null) {
            Statement st = null;
            try {
                st = conn.createStatement();
                ResultSet rs = st.executeQuery("show master status");
                rs.first();
                dbMasterStatus = new DBMasterStatus();
                // 将rs对象中字段映射到dbSlaveStatus对象中
                ResultSetMetaData metaData = rs.getMetaData();
                for (int i = 1; i < metaData.getColumnCount() + 1; i++) {
                    BeanUtils.setProperty(dbMasterStatus, metaData.getColumnName(i).toLowerCase(), rs.getObject(i));
                }
                return dbMasterStatus;
            } catch (SQLException e) {
                e.printStackTrace();
                return null;
            } catch (IllegalAccessException e) {
                e.printStackTrace();
                return null;
            } catch (InvocationTargetException e) {
                e.printStackTrace();
                return null;
            }
        }
        return null;
    }


    public String transferDbServerToSqlStatement(DBServer server){
        Integer id = server.getId();
        String serverName = server.getServerName();
        String ip = server.getServerIp();
        Integer port = server.getServerPort();
        Integer serverStatus = server.getServerStatus();
        Integer serverIdentity = server.getServerIdentity();
        Integer masterServerId = server.getMasterServerId();
        String username = server.getUsername();
        String pass = server.getPass();
        String sql = String.format("INSERT INTO `db_server` VALUES ('%d', '%s', '%s', '%d', '%d', '%d', null, '%s', '%s');",
                id, serverName, ip, port, serverStatus, serverIdentity, username, pass);
        return sql;
    }
}
